package yourapp.expense.dao;

import yourapp.expense.entity.Expense;
import yourapp.expense.service.QueryService;
import yourapp.expense.util.ExpenseResultParser;

import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

public class ExpenseDAO {

  private Connection connection;


  public ExpenseDAO() {
    // 使用 DatabaseConnection 单例获取数据库连接
    this.connection = DatabaseConnection.getInstance().getConnection();
  }

  /**
   * 插入一个新的支出记录到数据库
   * @param expense 要插入的支出记录对象
   */
  public void insertExpense(Expense expense) {
    // 定义插入支出记录的 SQL 语句
    // 假设 Expense 表有字段：amount, expense_date, description, payment_app_category_id, shopping_app_category_id, purpose_category_id
    String sql = "INSERT INTO Expense (amount, expense_date, description, payment_app_category_id, shopping_app_category_id, purpose_category_id) " +
        "VALUES (?, ?, ?, ?, ?, ?)";

    try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
      // 设置 SQL 参数
      pstmt.setBigDecimal(1, expense.getAmount());
      pstmt.setDate(2, new java.sql.Date(expense.getExpenseDate().getTime()));
      pstmt.setString(3, expense.getDescription());

      // 设置可为 null 的外键字段
      if (expense.getPaymentAppCategory() != null) {
        pstmt.setInt(4, expense.getPaymentAppCategory().getPaymentAppCategoryId());
      } else {
        pstmt.setNull(4, java.sql.Types.INTEGER);
      }

      if (expense.getShoppingAppCategory() != null) {
        pstmt.setInt(5, expense.getShoppingAppCategory().getShoppingAppCategoryId());
      } else {
        pstmt.setNull(5, java.sql.Types.INTEGER);
      }

      pstmt.setInt(6, expense.getPurposeCategory().getPurposeCategoryId());

      // 执行插入操作
      pstmt.executeUpdate();
      System.out.println("✅ 支出记录插入成功");
    } catch (SQLException e) {
      // 捕获并处理 SQL 异常
      System.err.println("❌ 插入支出记录失败: " + e.getMessage());
      e.printStackTrace();
    }
  }

  /**
   * 从 ExpenseWithCategoryNames 视图中获取最近的八条支出记录
   * @return 包含最近八条支出记录的列表
   */
  public List<Expense> fetchRecentExpensesFromView() {
    List<Expense> expenses = new ArrayList<>();
    String sql = "SELECT * FROM ExpenseWithCategoryNames LIMIT 8";

    try (Statement stmt = connection.createStatement();
         ResultSet rs = stmt.executeQuery(sql)) {

      expenses = ExpenseResultParser.parseExpensesFromResultSet(rs);
    } catch (SQLException e) {
      System.err.println("获取最近支出记录失败: " + e.getMessage());
      e.printStackTrace();
    }

    return expenses;
  }
  // ExpenseDAO.java 新增方法

  // 根据ID查支出记录
  public Expense getExpenseById(int expenseId) {
    Expense expense = null;
    String sql = "SELECT * FROM Expense WHERE expense_id = ?";
    try (
         PreparedStatement pstmt = connection.prepareStatement(sql)) {
      pstmt.setInt(1, expenseId);
      ResultSet rs = pstmt.executeQuery();
      if (rs.next()) {
        expense = new Expense();
        expense.setExpenseId(rs.getInt("expense_id"));
        expense.setAmount(rs.getBigDecimal("amount"));
        expense.setExpenseDate(rs.getDate("expense_date"));
        expense.setDescription(rs.getString("description"));
        // 其它字段根据需要补充（比如分类ID...）
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return expense;
  }

  // 根据ID删支出记录
  public void deleteExpense(int expenseId) {
    String sql = "DELETE FROM Expense WHERE expense_id = ?";
    try (
         PreparedStatement pstmt = connection.prepareStatement(sql)) {
      pstmt.setInt(1, expenseId);
      pstmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
  // ExpenseDAO.java 新增方法

  public Expense getViewById(int expenseId) {
    Expense expense = null;
    String sql = "SELECT * FROM ExpenseWithCategoryNames WHERE expense_id = ?";
    try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
      pstmt.setInt(1, expenseId);
      ResultSet rs = pstmt.executeQuery();
      if (rs.next()) {
        expense = new Expense();
        expense.setExpenseId(rs.getInt("expense_id"));
        expense.setAmount(rs.getBigDecimal("amount"));
        expense.setExpenseDate(rs.getDate("expense_date"));
        expense.setDescription(rs.getString("description"));
        expense.setPaymentAppName(rs.getString("payment_app_name"));
        expense.setShoppingAppName(rs.getString("shopping_app_name"));
        expense.setPurposeCategoryName(rs.getString("purpose_category_name"));
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return expense;
  }

  public void updateAmount(Expense expense) {
    String sql = "UPDATE Expense SET amount = ? WHERE expense_id = ?";
    try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
      pstmt.setBigDecimal(1, expense.getAmount());
      pstmt.setInt(2, expense.getExpenseId());
      pstmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public void updateExpenseDate(Expense expense) {
    String sql = "UPDATE Expense SET expense_date = ? WHERE expense_id = ?";
    try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
      pstmt.setDate(1, new java.sql.Date(expense.getExpenseDate().getTime()));
      pstmt.setInt(2, expense.getExpenseId());
      pstmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public void updateDescription(Expense expense) {
    String sql = "UPDATE Expense SET description = ? WHERE expense_id = ?";
    try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
      pstmt.setString(1, expense.getDescription());
      pstmt.setInt(2, expense.getExpenseId());
      pstmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public void updatePaymentAppCategory(Expense expense) {
    String sql = "UPDATE Expense SET payment_app_category_id = ? WHERE expense_id = ?";
    try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
      if (expense.getPaymentAppCategory() != null) {
        pstmt.setInt(1, expense.getPaymentAppCategory().getPaymentAppCategoryId());
      } else {
        pstmt.setNull(1, java.sql.Types.INTEGER);
      }
      pstmt.setInt(2, expense.getExpenseId());
      pstmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public void updateShoppingAppCategory(Expense expense) {
    String sql = "UPDATE Expense SET shopping_app_category_id = ? WHERE expense_id = ?";
    try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
      if (expense.getShoppingAppCategory() != null) {
        pstmt.setInt(1, expense.getShoppingAppCategory().getShoppingAppCategoryId());
      } else {
        pstmt.setNull(1, java.sql.Types.INTEGER);
      }
      pstmt.setInt(2, expense.getExpenseId());
      pstmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public void updatePurposeCategory(Expense expense) {
    String sql = "UPDATE Expense SET purpose_category_id = ? WHERE expense_id = ?";
    try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
      pstmt.setInt(1, expense.getPurposeCategory().getPurposeCategoryId());
      pstmt.setInt(2, expense.getExpenseId());
      pstmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
  //按日期查询并返回记录
  // ExpenseDAO.java 新增方法

  public List<Expense> getExpensesByYear(int year) {
    List<Expense> expenses = new ArrayList<>();
    String sql = "SELECT * FROM ExpenseWithCategoryNames WHERE YEAR(expense_date) = ?";
    try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
      pstmt.setInt(1, year);
      ResultSet rs = pstmt.executeQuery();
      expenses = ExpenseResultParser.parseExpensesFromResultSet(rs); // ← 封装方法调用
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return expenses;
  }

  public List<Expense> getExpensesByMonth(int year, int month) {
    List<Expense> expenses = new ArrayList<>();
    String sql = "SELECT * FROM ExpenseWithCategoryNames WHERE YEAR(expense_date) = ? AND MONTH(expense_date) = ?";
    try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
      pstmt.setInt(1, year);
      pstmt.setInt(2, month);
      ResultSet rs = pstmt.executeQuery();
      expenses = ExpenseResultParser.parseExpensesFromResultSet(rs);
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return expenses;
  }

  public List<Expense> getExpensesByDay(int year, int month, int day) {
    List<Expense> expenses = new ArrayList<>();
    String sql = "SELECT * FROM ExpenseWithCategoryNames WHERE YEAR(expense_date) = ? AND MONTH(expense_date) = ? AND DAY(expense_date) = ?";
    try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
      pstmt.setInt(1, year);
      pstmt.setInt(2, month);
      pstmt.setInt(3, day);
      ResultSet rs = pstmt.executeQuery();
      expenses = ExpenseResultParser.parseExpensesFromResultSet(rs);
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return expenses;
  }
  public List<Expense> getExpensesByDays(List<Date> dates) {
    List<Expense> allExpenses = new ArrayList<>();
    for (Date date : dates) {
      Calendar cal = Calendar.getInstance();
      cal.setTime(date);
      int year = cal.get(Calendar.YEAR);
      int month = cal.get(Calendar.MONTH) + 1;
      int day = cal.get(Calendar.DAY_OF_MONTH);
      allExpenses.addAll(getExpensesByDay(year, month, day));
    }
    return allExpenses;
  }
}